I'm developing an emulator for an MMO and I have a question:
Load or not all account data(storage items, all character info, etc) when user log-in?

at the moment what I'm doing is loading only the required information:

on request login: load account data (storage items, gold, etc)
on send character list: load basic character info (equip items(only item/app id/refine), and infos that's displayed on the character screen).
on send to world: load all character info (inventory items, mail, friend list, equip items, skills etc).

the problem is that I think I'm running many queries to the database. ex: 

on delete character: check character PIN, on error, update pin error count.
on create character: insert character info, basic items on db, send character list
on back to charlist: save all data of current online character, send character list

I'm using an asynchronous connection model for the clients. with many queries being executed at the same time the server will block.

would someone have a better way to do this?

Obs: I'm using boost::asio for the connection and mysql for the database.

23 minutes ago, aikaproject said:

the problem is that I think I'm running many queries to the database. ex: 

The problem is that you are not measuring and using empirical evidence to pinpoint the root cause of your situation ;-)

Most MMO architectures separate database operations from game operations because it makes it easier to think of the database as an expensive resource. You "can" block on transactions but you should design your architecture such that your default assumption is that hitting the DB is slow and error-prone. This makes it easier to react appropriately when errors or timeouts occur.

Part of designing a robust system is knowing its failure modes. This means understanding why a lot of DB queries lead to issues in your game, for example. Then you can determine the correct response. Sometimes you need to optimize DB procedures, or add indexes, or build caching on top of the DB layer, or just lower your expectations :-)

Your approach sounds sensible (only load the data that's needed), but I would agree with ApochPiQ, why do you think your database is getting too many queries? Modern RDBMS can handle a LOT of concurrency (especially reads), so I would not assume you are doing too many unless you profile and find DB queries are taking a long time. (You can of course usually run queries on the DB itself to see what the load stats look like).

How many concurrent players do you have?

@ApochPiQ @Redskyforge thanks for reply.

Quote

the problem is that I think I'm running many queries to the database. ex: 

Sorry, but I ended up expressing wrong about the problem.
I noticed that my problem is when inserting or deleting data (INSERT, DELETE).
The blocking is +/- 300ms and I think it's too much for an asynchronous server.

At the moment, I'm doing what apoch said and implementing a 'data server', separating the database from the game server.

You could take a look at my latest blog post where I'm trying to describe how I've been separating DB from simulation and networking. I haven't done any work on performance whatsoever - I just write accounts and players to JSON files - and it's already possible to update persist more than 1000 players/second. With a database I'm sure you could do it much faster (at least as long as you don't normalize more than needed).

But my main goal wasn't even performance as such - my main goal was to never let a networking or game simulation thread wait for DB operations. There are many ways to do this - continuation lambdas, state machines, async/await etc.

In particular I recommend reading the 2 articles on ithare.com that I'm linking to. They might be a bit long-winded but really worth a read. Actually I recommend reading a lot of the articles on that site - the author is writing a book series on MMO development.

Databases aren't necessarily faster than file systems. Instead, they provide features file systems don't, like block coalescing for small data items, and multiple indexing, and transparent management of millions of items, and transactional semantics.

Try creating a directory with a million files in it. Most file systems will choke and slow down tremendously.

Also, to actually get transactional update of a file, you need to:

1) write to a temp file

2) call fsync() and fdatasync() and close()

3) call rename() to replace the old file with the temp file

4) call sync()

And sync() is asynchronous, so you don't REALLY know that the rename was actually persistently committed. But at least you'll be in one of two states: old file, or new file, without any half-files.

(This is on UNIX -- Windows has similar system calls available)

True if I work on performance I can probably make things work faster with a DIY database, but with a DB I'm sure I can make things faster than what I have now (160000 individual files in a single folder)